<?

include_once 'models/Event.php';
include_once 'models/Role.php';
include_once 'models/User.php';

include_once 'private.inc.php';

function db_connect() {
  $link = mysql_connect("localhost", MYSQL_ID, MYSQL_PASSWD) 
    or die ("Could not connect: ".mysql_error());
  mysql_select_db(DB_NAME) 
    or die ("Could not select database: ".DB_NAME);
  return $link;
}

function db_disconnect($link) {
  mysql_close($link);
}



// ----- user functions -----

function db_getAllUsers() {
  $users = array();
  $query = "SELECT * FROM `".USER_TABLE."` ORDER BY `id`;";
  $result = mysql_query($query) or die(mysql_error());
  $idx = 1;
  while ($line = mysql_fetch_assoc($result)) {
    $user = new User();
    $user->setAttributes($line);
    $users[$idx++] = $user;
  }
  return $users;
}

function db_getUser($id) {
  $id = mysql_real_escape_string($id);
  $query = "SELECT * FROM `".USER_TABLE."` WHERE `id`=".$id.";";
  $result = mysql_query($query) or die(mysql_error());
  if ($line = mysql_fetch_assoc($result)) {
    $user = new User();
    $user->setAttributes($line);
  }
  else {
    return NULL;
  }
  return $user;
}

function db_getUserByEmail($email) {
  $email = mysql_real_escape_string($email);
  $query = "SELECT * FROM `".USER_TABLE."` WHERE `email`='".$email."';";
  $result = mysql_query($query) or die(mysql_error());
  if ($line = mysql_fetch_assoc($result)) {
    $user = new User();
    $user->setAttributes($line);
  }
  else {
    return NULL;
  }
  return $user;
}

function db_createUser($user) {
  $email = mysql_real_escape_string($user->getEmail());
  $password = mysql_real_escape_string($user->getPassword());
  $name = mysql_real_escape_string($user->getName());
  $address = mysql_real_escape_string($user->getAddress());
  $phone = mysql_real_escape_string($user->getPhone());
  
  $query = "INSERT INTO `".USER_TABLE."`".
           " (`email`, `password`, `name`, `address`, `phone`)".
           " VALUES".
           " ('".$email."', '".$password."', '".$name."', '".$address."', '".
                 $phone."');";
  mysql_query($query) or die (mysql_error());
  
  $result = mysql_query("SELECT `id` FROM `".USER_TABLE."` ORDER BY `id` DESC LIMIT 1;") or die (mysql_error());
  $line = mysql_fetch_assoc($result);
  return $line["id"];
}

function db_updateUser($user) {
  $id = mysql_real_escape_string($user->getID());
  $email = mysql_real_escape_string($user->getEmail());
  $password = mysql_real_escape_string($user->getPassword());
  $name = mysql_real_escape_string($user->getName());
  $address = mysql_real_escape_string($user->getAddress());
  $phone = mysql_real_escape_string($user->getPhone());
  
  $query = "UPDATE `".USER_TABLE."` SET `email`='".$email."', ".
           "`password`='".$password."', ".
           "`name`='".$name."', ".
           "`address`='".$address."', ".
           "`phone`='".$phone."' ".
           "WHERE `id`=".$id.";";
  mysql_query($query) or die (mysql_error());
}

function db_deleteUser($user) {
  $id = mysql_real_escape_string($user->getID());
  $query = "DELETE FROM `".USER_TABLE."` WHERE `id`=".$id.";";
  mysql_query($query) or die (mysql_error());
}



// ----- event functions -----

function db_getAllEvents() {
  $events = array();
  $query = "SELECT * FROM `".EVENT_TABLE."` ORDER BY `id`;";
  $result = mysql_query($query) or die(mysql_error());
  $idx = 1;
  while ($line = mysql_fetch_assoc($result)) {
    $line['start'] = new DateTime($line['start']);
    $line['done'] = new DateTime($line['done']);
    $event = new Event();
    $event->setAttributes($line);
    $events[$idx++] = $event;
  }
  return $events;
}

function db_getEvent($id) {
  $id = mysql_real_escape_string($id);
  $query = "SELECT * FROM `".EVENT_TABLE."` WHERE `id`=".$id.";";
  $result = mysql_query($query) or die(mysql_error());
  if ($line = mysql_fetch_assoc($result)) {
    $line['start'] = new DateTime($line['start']);
    $line['done'] = new DateTime($line['done']);
    $event = new Event();
    $event->setAttributes($line);
  }
  else {
    return NULL;
  }
  return $event;
}

function db_createEvent($event) {
  $name = mysql_real_escape_string($event->getName());
  $description = mysql_real_escape_string($event->getDescription());
  $start_time = $event->getStartTime();
  $end_time = $event->getEndTime();
  $location = mysql_real_escape_string($event->getLocation());
  
  $query = "INSERT INTO `".EVENT_TABLE."`".
           " (`name`, `description`, `start`, `done`, `location`)".
           " VALUES".
           " ('".$name."', '".$description."', '".$start_time->format('Y-m-d H:i:s')."', '".
                 $end_time->format('Y-m-d H:i:s')."', '".$location."');";
  mysql_query($query) or die (mysql_error());
  
  $result = mysql_query("SELECT `id` FROM `".EVENT_TABLE."` ORDER BY `id` DESC LIMIT 1;") or die (mysql_error());
  $line = mysql_fetch_assoc($result);
  return $line["id"];
}

function db_updateEvent($event) {
  $id = mysql_real_escape_string($event->getID());
  $name = mysql_real_escape_string($event->getName());
  $description = mysql_real_escape_string($event->getDescription());
  $start_time = $event->getStartTime();
  $end_time = $event->getEndTime();
  $location = mysql_real_escape_string($event->getLocation());
  
  $query = "UPDATE `".EVENT_TABLE."` SET `name`='".$name."', ".
           "`description`='".$description."', ".
           "`start`='".$start_time->format('Y-m-d H:i:s')."', ".
           "`done`='".$end_time->format('Y-m-d H:i:s')."', ".
           "`location`='".$location."' ".
           "WHERE `id`=".$id.";";
  mysql_query($query) or die (mysql_error());
}

function db_deleteEvent($event) {
  $id = mysql_real_escape_string($event->getID());
  $query = "DELETE FROM `".EVENT_TABLE."` WHERE `id`=".$id.";";
  mysql_query($query) or die (mysql_error());
}



// ----- role functions -----

function db_getRolesForUser($user) {
  $id = mysql_real_escape_string($user->getID());
  $roles = array();
  $query = "SELECT * FROM `".ROLE_TABLE."` WHERE `person_id`=".$id." ORDER BY `id`;";
  $result = mysql_query($query) or die(mysql_error());
  $idx = 1;
  while ($line = mysql_fetch_assoc($result)) {
    $role = new Role();
    $role->setAttributes($line);
    $roles[$idx++] = $role;
  }
  return $roles;
}

function db_getRolesForEvent($event) {
  $id = mysql_real_escape_string($event->getID());
  $roles = array();
  $query = "SELECT * FROM `".ROLE_TABLE."` WHERE `activity_id`=".$id." ORDER BY `id`;";
  $result = mysql_query($query) or die(mysql_error());
  $idx = 1;
  while ($line = mysql_fetch_assoc($result)) {
    $role = new Role();
    $role->setAttributes($line);
    $roles[$idx++] = $role;
  }
  return $roles;
}

function db_getRole($id) {
  $id = mysql_real_escape_string($id);
  $query = "SELECT * FROM `".ROLE_TABLE."` WHERE `id`=".$id.";";
  $result = mysql_query($query) or die(mysql_error());
  if ($line = mysql_fetch_assoc($result)) {
    $role = new Role();
    $role->setAttributes($line);
  }
  else {
    return NULL;
  }
  return $role;
}

function db_createRole($role) {
  $name = mysql_real_escape_string($role->getName());
  $user_id = mysql_real_escape_string($role->getUserID());
  $event_id = mysql_real_escape_string($role->getEventID());
  
  $query = "INSERT INTO `".ROLE_TABLE."`".
           " (`name`, `person_id`, `activity_id`)".
           " VALUES".
           " ('".$name."', '".$user_id."', '".$event_id."');";
  mysql_query($query) or die (mysql_error());
  
  $result = mysql_query("SELECT `id` FROM `".ROLE_TABLE."` ORDER BY `id` DESC LIMIT 1;") or die (mysql_error());
  $line = mysql_fetch_assoc($result);
  return $line["id"];
}

function db_updateRole($role) {
  $id = mysql_real_escape_string($role->getID());
  $name = mysql_real_escape_string($role->getName());
  $user_id = mysql_real_escape_string($role->getUserID());
  $event_id = mysql_real_escape_string($role->getEventID());
  
  $query = "UPDATE `".ROLE_TABLE."` SET `name`='".$name."', ".
           "`person_id`='".$user_id."', ".
           "`activity_id`='".$event_id."' ".
           "WHERE `id`=".$id.";";
  mysql_query($query) or die (mysql_error());
}

function db_deleteRole($role) {
  $id = mysql_real_escape_string($role->getID());
  $query = "DELETE FROM `".ROLE_TABLE."` WHERE `id`=".$id.";";
  mysql_query($query) or die (mysql_error());
}

?>
